import pymysql
# xlrd 为 python 中读取 excel 的库，支持.xls 和 .xlsx 文件
import xlrd
from datetime import datetime


# openpyxl 库支持 .xlsx 文件的读写
# from openpyxl.reader.excel import load_workbook
# from builtins import int


# cur 是数据库的游标链接，path 是 excel 文件的路径
def importExcelToMysql(cur, path):
    ### xlrd版本
    # 读取excel文件
    print("***")
    workbook = xlrd.open_workbook(path)
    print("****")
    print(workbook)
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[0])
    print(worksheet)
    ###

    ### openpyxl版本
    # 读取excel文件
    # workbook = load_workbook(path)
    # # 获得所有工作表的名字
    # sheets = workbook.get_sheet_names()
    # # 获得第一张表
    # worksheet = workbook.get_sheet_by_name(sheets[0])
    ###

    ### xlrd版本
    # 将表中数据读到 sqlstr 数组中
    for i in range(1, worksheet.nrows):
        row = worksheet.row(i)
        print(i)

        sqlstr = []

        for j in range(1, worksheet.ncols):
            sqlstr.append(worksheet.cell_value(i, j))
        print("*****")
        # print(sqlstr[0])
        # print(sqlstr[1])
        # print(sqlstr[2])
        # print(sqlstr[3])
        # print(sqlstr[4])
        # print(sqlstr[5])
        # print(sqlstr[6])
        # print(sqlstr[7])
        # print(sqlstr[8])
        # print(sqlstr[9])
        # print(sqlstr[10])
        # print(sqlstr[11])
        # print(sqlstr[12])
        # print(sqlstr[13])
        # print(sqlstr[14])
        # print(sqlstr[15])
        # print(sqlstr[16])
        # print(sqlstr[17])
        # print(sqlstr[18])
        # print(sqlstr[19])
        # print(sqlstr[20])
        # print(sqlstr[21])
        # print(sqlstr[22])
        # print(sqlstr[23])
        # print(sqlstr[24])
        # print(sqlstr[25])
        # print(sqlstr[26])
        # print(sqlstr[27])
        # print(sqlstr[28])

        valuestr = [str(sqlstr[0]),
                    str(sqlstr[1]),
                    str(sqlstr[2]),
                    str(sqlstr[3]),  # brand name asin category
                    0 if sqlstr[4] == '-' or sqlstr[4] == "" else float(sqlstr[4]),   # price(decimal)
                    0 if sqlstr[5] == '-' or sqlstr[5] == "" else float(sqlstr[5]),   # buy_box_price(decimal)
                    0 if sqlstr[6] == '-' or sqlstr[6] == "" else float(sqlstr[6]),   # ofter_min_price(decimal)
                    0 if sqlstr[7] == '-' or sqlstr[7] == "" else float(sqlstr[7]),   # ofter(decimal)
                    "" if sqlstr[8] == '-' or sqlstr[8] == "" else int(sqlstr[8]),    # comments(int)
                    str(sqlstr[9]),    # good_version
                    1 if sqlstr[10] == "采集完成" else 0,  # status (smallint)
                    str(sqlstr[11]),  # color
                    str(sqlstr[12]),  # size
                    str(sqlstr[13]),  # country
                    0 if sqlstr[14] == '-' or sqlstr[14] == "" else float(sqlstr[14]),  # star(decimal)
                    str(sqlstr[15]),  # seller_type

                    1 if sqlstr[16] == "Y" else 0,  # haven_cart(tinyint)
                    str(sqlstr[17]),  # small_image_url
                    str(sqlstr[18]),
                    str(sqlstr[19]),
                    str(sqlstr[20]),
                    str(sqlstr[21]),
                    str(sqlstr[22]),
                    str(sqlstr[23]),
                    str(sqlstr[24]),
                    str(sqlstr[25]),
                    str(sqlstr[26]),
                    str(sqlstr[27]),
                    str(sqlstr[28]), ]
        print("这是valuestr")

        cur.execute("insert into tb_sku(brand, name, asin, category,price, buy_box_price,offer_min_price, offer, comments, good_version, status, color, size, country,star, seller_type, haven_cart, product_url, small_image_url, heavy_image_url,local_image_url, remark, goods_short_desc, goods_long_desc, task,get_time,best_seller_rank,can_sell_info, general_info) values(%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s )",valuestr)
        ###
        print("**")

        ### openpyxl版本
        # 将表中每一行数据读到 sqlstr 数组中
        # for row in worksheet.rows:
        #
        #     sqlstr = []
        #
        #     for cell in row:
        #         sqlstr.append(cell.value)
        #     ###
        #
        #     valuestr = [str(sqlstr[0]), int(sqlstr[1]), int(sqlstr[2]), int(sqlstr[3])]
        #
        #     # 将每行数据存到数据库中
        #     cur.execute("insert into student(姓名, 语文, 数学, 英语) values(%s, %s, %s, %s)", valuestr)

        # 输出数据库中内容

    # def readTable(cursor):
    #     # 选择全部
    #     cursor.execute("select * from student")
    #     # 获得返回值，返回多条记录，若没有结果则返回()
    #     results = cursor.fetchall()
    #
    #     for i in range(0, results.__len__()):
    #         for j in range(0, 4):
    #             print(results[i][j], end='\t')
    #
    #         print('\n')


if __name__ == '__main__':
    # 和数据库建立连接
    conn = pymysql.connect('localhost', 'vne123', '123', charset='utf8')

    # 创建游标链接
    cur = conn.cursor()


    # 新建一个database
    # cur.execute("drop database if exists students")
    # cur.execute("create database students")
    # 选择 students 这个数据库
    cur.execute("use vnemy")


    # sql中的内容为创建一个名为student的表
    # sql = """CREATE TABLE IF NOT EXISTS `student` (
    #             `姓名` VARCHAR (20),
    #             `语文` INT,
    #             `数学` INT,
    #             `英语` INT
    #           )"""
    # 如果存在student这个表则删除
    # cur.execute("drop table if exists student")
    # 创建表
    # cur.execute(sql)

    # 将 excel 中的数据导入 数据库中
    importExcelToMysql(cur, "./test3.xls")
    # readTable(cur)

    # 关闭游标链接
    cur.close()
    conn.commit()
    # 关闭数据库服务器连接，释放内存
    conn.close()
# ---------------------
# 作者：Anwel
# 来源：CSDN
# 原文：https: // blog.csdn.net / Anwel / article / details / 79967261
# 版权声明：本文为博主原创文章，转载请附上博文链接！
